package db.queries;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import core.common.Configurations;
import core.exceptions.DBAccessException;
import core.objects.NewsFeedItem;
import core.objects.enums.NewsFeedEnum;
import db.DBHandler;
import db.IDBQuery;
import db.IQueryBuilder;
import db.dbTables.DbColumn;
import db.queries.core.QueriesEnvironment;

public class NewsFeedQueries implements IDBQuery<NewsFeedItem> {

	private static final String ALIAS_COLUMN_TYPE = "type";
	private static final String ALIAS_COLUMN_USERNAME = "username";
	private static final String ALIAS_COLUMN_FIRSTNAME = "firstname";
	private static final String ALIAS_COLUMN_LASTNAME = "lastname";
	private static final String ALIAS_COLUMN_BOOKID = "bookid";
	private static final String ALIAS_COLUMN_BOOKNAME = "bookname";
	private static final String ALIAS_COLUMN_COMMENT_USERNAME = "comment_username";
	private static final String ALIAS_COLUMN_COMMENT_FIRSTNAME = "comment_firstname";
	private static final String ALIAS_COLUMN_COMMENT_LASTNAME = "comment_lastname";
	private static final String ALIAS_COLUMN_COMMENT_RATING = "comment_rating";
	private static final String ALIAS_COLUMN_COMMENT = "comment";
	private static final String ALIAS_COLUMN_START_DATE = "startdate";
	
	/* all of the parameters should recieve the same value (userId), which
	 * is why we keep a head count to insert with a loop, rather than manually: */
	private static final int NUM_OF_PREPARED_STATEMENT_PARAMS = 14;
	
	@Override
	public List<NewsFeedItem> load(int id) throws DBAccessException {
		try {
			
			ResultSet rs = DBHandler.executeQuery(buildNewsFeedQuery(id));
			
			ArrayList<NewsFeedItem> feed = new ArrayList<NewsFeedItem>();
			
			while (rs.next()) {
			
				try {

					NewsFeedItem feedItem = new NewsFeedItem(0);
		
					//extract type:
					feedItem.setType(NewsFeedEnum.parse(rs.getInt(ALIAS_COLUMN_TYPE)));
					
					//extract user info:
					feedItem.setUsername(rs.getString(ALIAS_COLUMN_USERNAME));
					feedItem.setFirstname(rs.getString(ALIAS_COLUMN_FIRSTNAME));
					feedItem.setLastname(rs.getString(ALIAS_COLUMN_LASTNAME));
					
					//extract book info:
					feedItem.setBookID(rs.getInt(ALIAS_COLUMN_BOOKID));
					feedItem.setBookName(rs.getString(ALIAS_COLUMN_BOOKNAME));
					
					//extract user comment info:
					feedItem.setCommentUsername(rs.getString(ALIAS_COLUMN_COMMENT_USERNAME));
					feedItem.setCommentfirstname(rs.getString(ALIAS_COLUMN_COMMENT_FIRSTNAME));
					feedItem.setCommentlastname(rs.getString(ALIAS_COLUMN_COMMENT_LASTNAME));
					
					//extract general comment info:
					feedItem.setCommentRating(rs.getInt(ALIAS_COLUMN_COMMENT_RATING));
					feedItem.setComment(rs.getString(ALIAS_COLUMN_COMMENT));
					
					//finally, extract date:
					feedItem.setStartDate(rs.getTimestamp(ALIAS_COLUMN_START_DATE));
					
					feed.add(feedItem);
				
				} catch (Exception e) {
					Logger.getLogger("NewsFeed").log(Level.SEVERE, null, e);
				}
				
				
			}
			
			return feed;
			
		} catch (SQLException exception) {
			throw new DBAccessException("Could not load news feed, unknown error has occurred.", exception);
		}
	}

	private IQueryBuilder buildNewsFeedQuery(final int id) {
		return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				for (int i = 1; i <= NUM_OF_PREPARED_STATEMENT_PARAMS; i++) {
					statement.setInt(i, id);
				}
			}
			
			@Override
			public String buildQuery() {

				StringBuilder builder = new StringBuilder();
				
				builder.append("SELECT DISTINCT * FROM ( ");
				builder.append(buildQuery1());
				builder.append(" UNION ");
				builder.append(buildQuery2());
				builder.append(" UNION ");
				builder.append(buildQuery3());
				builder.append(" UNION ");
				builder.append(buildQuery4());
				builder.append(" UNION ");
				builder.append(buildQuery5());
				builder.append(" UNION ");
				builder.append(buildQuery6());
				builder.append(" UNION ");
				builder.append(buildQuery7());
				builder.append(" UNION ");
				builder.append(buildQuery8());
				builder.append(") AS feed ORDER BY ");
				builder.append(ALIAS_COLUMN_START_DATE);
				builder.append(" DESC LIMIT ");
				builder.append(Configurations.getNewsFeedLimit());
				builder.append(";");
								
				return builder.toString();
				
			}
		};
	}
	
	/* 1st query - returns which books the people who loaned from me
	 * have loaned to others: */
	private String buildQuery1() {
		return buildBookLoaningQuery(
				NewsFeedEnum.LoanedFromMeLoaning,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanedId,
				QueriesEnvironment.getLoanTable().loanedId,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanedId);
	}
	
	/* 2nd query - returns recent comments on books user has commented on: */
	private String buildQuery2() {
		
		StringBuilder builder = new StringBuilder();
		
		builder.append("SELECT ");
		builder.append(NewsFeedEnum.BookICommentedOnComment.get());
		builder.append(" AS " + ALIAS_COLUMN_TYPE);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().username.getName());
		builder.append(" AS " + ALIAS_COLUMN_USERNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().first_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_FIRSTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().last_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_LASTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getBookTable().id.getName());
		builder.append(" AS " + ALIAS_COLUMN_BOOKID);
		builder.append(", ");
		builder.append(QueriesEnvironment.getBookTable().name.getName());
		builder.append(" AS " + ALIAS_COLUMN_BOOKNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_USERNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_FIRSTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_LASTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getBookCommentTable().rating.getName());
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_RATING);
		builder.append(", ");
		builder.append(QueriesEnvironment.getBookCommentTable().comment.getName());
		builder.append(" AS " + ALIAS_COLUMN_COMMENT);
		builder.append(", ");
		builder.append(QueriesEnvironment.getBookCommentTable().timestamp.getName());
		builder.append(" AS " + ALIAS_COLUMN_START_DATE);

		builder.append(" FROM ");
		builder.append(QueriesEnvironment.getBookCommentTable().getName());
		builder.append(", ");
		builder.append("(SELECT * FROM ");
		builder.append(QueriesEnvironment.getBookCommentTable().getName());
		builder.append(" WHERE ");
		builder.append(QueriesEnvironment.getBookCommentTable().commenterId.getName());
		builder.append(" = ? ORDER BY ");
		builder.append(QueriesEnvironment.getBookCommentTable().timestamp.getName());
		builder.append(" DESC) AS commented_on, ");
		
		builder.append(QueriesEnvironment.getBookTable().getName());
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().getName());
		
		builder.append(" WHERE ");
		builder.append(QueriesEnvironment.getBookCommentTable().bookId.getName());
		builder.append(" = commented_on.");
		builder.append(QueriesEnvironment.getBookCommentTable().bookId.getName().
				substring(QueriesEnvironment.getBookCommentTable().getName().length() + 1));
		builder.append(" AND ");
		builder.append(QueriesEnvironment.getBookCommentTable().commenterId.getName());
		builder.append(" != ? AND ");
		builder.append(QueriesEnvironment.getBookTable().id.getName());
		builder.append(" = ");
		builder.append(QueriesEnvironment.getBookCommentTable().bookId.getName());
		builder.append(" AND ");
		builder.append(QueriesEnvironment.getBookCommentTable().commenterId.getName());
		builder.append(" = ");
		builder.append(QueriesEnvironment.getUserTable().id.getName());
		
		return builder.toString();
	}

	/* 3rd query - comments recently made on users i've commented on: */
	private String buildQuery3() {
		
		StringBuilder builder = new StringBuilder();
		
		int userTableNameLen = QueriesEnvironment.getUserTable().getName().length() + 1;
		
		builder.append("SELECT ");
		builder.append(NewsFeedEnum.UserICommentedOnComment.get());
		builder.append(" AS " + ALIAS_COLUMN_TYPE);
		builder.append(", ");
		builder.append("commenting_user.");
		builder.append(QueriesEnvironment.getUserTable().username.getName().
				substring(userTableNameLen));
		builder.append(" AS " + ALIAS_COLUMN_USERNAME);
		builder.append(", ");
		builder.append("commenting_user.");
		builder.append(QueriesEnvironment.getUserTable().first_name.getName().
				substring(userTableNameLen));
		builder.append(" AS " + ALIAS_COLUMN_FIRSTNAME);
		builder.append(", ");
		builder.append("commenting_user.");
		builder.append(QueriesEnvironment.getUserTable().last_name.getName().
				substring(userTableNameLen));
		builder.append(" AS " + ALIAS_COLUMN_LASTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_BOOKID);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_BOOKNAME);
		builder.append(", ");
		builder.append("commented_on_user.");
		builder.append(QueriesEnvironment.getUserTable().username.getName().
				substring(userTableNameLen));
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_USERNAME);
		builder.append(", ");
		builder.append("commented_on_user.");
		builder.append(QueriesEnvironment.getUserTable().first_name.getName().
				substring(userTableNameLen));
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_FIRSTNAME);
		builder.append(", ");
		builder.append("commented_on_user.");
		builder.append(QueriesEnvironment.getUserTable().last_name.getName().
				substring(userTableNameLen));
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_LASTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserCommentTable().rating.getName());
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_RATING);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserCommentTable().comment.getName());
		builder.append(" AS " + ALIAS_COLUMN_COMMENT);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserCommentTable().timestamp.getName());
		builder.append(" AS " + ALIAS_COLUMN_START_DATE);

		builder.append(" FROM ");
		builder.append(QueriesEnvironment.getUserCommentTable().getName());
		builder.append(", ");
		builder.append("(SELECT * FROM ");
		builder.append(QueriesEnvironment.getUserCommentTable().getName());
		builder.append(" WHERE ");
		builder.append(QueriesEnvironment.getUserCommentTable().commenterId.getName());
		builder.append(" = ? ORDER BY ");
		builder.append(QueriesEnvironment.getUserCommentTable().timestamp.getName());
		builder.append(" DESC) AS commented_on, ");
		
		builder.append(QueriesEnvironment.getUserTable().getName());
		builder.append(" AS commenting_user");
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().getName());
		builder.append(" AS commented_on_user");
		
		builder.append(" WHERE ");
		builder.append(QueriesEnvironment.getUserCommentTable().commenterId.getName());
		builder.append(" = commented_on.");
		builder.append(QueriesEnvironment.getUserCommentTable().commentedId.getName().
				substring(QueriesEnvironment.getUserCommentTable().getName().length() + 1));
		builder.append(" AND ");
		builder.append(QueriesEnvironment.getUserCommentTable().commenterId.getName());
		builder.append(" != ? AND ");
		builder.append(" commenting_user.");
		builder.append(QueriesEnvironment.getUserTable().id.getName().
				substring(userTableNameLen));
		builder.append(" = ");
		builder.append(QueriesEnvironment.getUserCommentTable().commenterId.getName());
		builder.append(" AND ");
		builder.append(QueriesEnvironment.getUserCommentTable().commentedId.getName());
		builder.append(" = commented_on_user.");
		builder.append(QueriesEnvironment.getUserTable().id.getName().
				substring(userTableNameLen));
		
		return builder.toString();
	}
	
	/*4th query - which books users i've loaned to are loaning themselves recently
	 * (from other users) */
	private String buildQuery4() {
		return buildBookLoaningQuery(
				NewsFeedEnum.LentFromAreLoaningFrom,
				QueriesEnvironment.getLoanTable().loanedId,
				QueriesEnvironment.getLoanTable().loanedId,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanedId);
	}
	
	/* 5th query - which books users i've loaned from are loaning themselves recently
	 * (from other users)
	 */
	private String buildQuery5() {
		return buildBookLoaningQuery(
				NewsFeedEnum.LoanedFromMeLoaningTo,
				QueriesEnvironment.getLoanTable().loanedId,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanedId,
				QueriesEnvironment.getLoanTable().loanedId);
	}

	/* 6th query - which books users i've loaned from are currently loaning
	 * to other users */
	private String buildQuery6() {
		return buildBookLoaningQuery(
				NewsFeedEnum.LentFromAreLoaningTo,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanedId,
				QueriesEnvironment.getLoanTable().loanerId,
				QueriesEnvironment.getLoanTable().loanerId);
	}
	
	/* 7th query - comments recently made about me */
	private String buildQuery7() {
		
		StringBuilder builder = new StringBuilder();
		
		builder.append("SELECT ");
		builder.append(NewsFeedEnum.CommentsAboutMe.get());
		builder.append(" AS " + ALIAS_COLUMN_TYPE);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().username.getName());
		builder.append(" AS " + ALIAS_COLUMN_USERNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().first_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_FIRSTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().last_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_LASTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_BOOKID);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_BOOKNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_USERNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_FIRSTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_LASTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserCommentTable().rating.getName());
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_RATING);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserCommentTable().comment.getName());
		builder.append(" AS " + ALIAS_COLUMN_COMMENT);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserCommentTable().timestamp.getName());
		builder.append(" AS " + ALIAS_COLUMN_START_DATE);
		
		builder.append(" FROM ");
		builder.append(QueriesEnvironment.getUserCommentTable().getName());
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().getName());
		builder.append(" WHERE ");
		builder.append(QueriesEnvironment.getUserCommentTable().commentedId.getName());
		builder.append(" = ? AND ");
		builder.append(QueriesEnvironment.getUserTable().id.getName());
		builder.append(" = ");
		builder.append(QueriesEnvironment.getUserCommentTable().commenterId.getName());
		
		return builder.toString();
	}

	private String buildQuery8() {
		
		StringBuilder builder = new StringBuilder();
		
		builder.append("SELECT ");
		builder.append(NewsFeedEnum.NewUser.get());
		builder.append(" AS " + ALIAS_COLUMN_TYPE);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().username.getName());
		builder.append(" AS " + ALIAS_COLUMN_USERNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().first_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_FIRSTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().last_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_LASTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_BOOKID);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_BOOKNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_USERNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_FIRSTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_LASTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_RATING);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().registerTime.getName());
		builder.append(" AS " + ALIAS_COLUMN_START_DATE);
		
		builder.append(" FROM ");
		builder.append(QueriesEnvironment.getUserTable().getName());
		builder.append(" WHERE ");
		builder.append(QueriesEnvironment.getUserTable().username.getName());
		builder.append(" != ? ");
		
		return builder.toString();
		
	}
	
	/**
	 * 
	 * builds a query searching for books in various situations based on the
	 * user having some sort of loaning/loaner interaction with the output.
	 * 
	 * Supply parameters so that:
	 * 		whereLoanLeft = whereLoanRight AND whereExclude != ?
	 * 	
	 * Also, when choosing which user to display supply whereUserComparison
	 * (to choose between loaner / loaned fields)
	 *
	 */
	private String buildBookLoaningQuery(NewsFeedEnum type, DbColumn innerQueryFilter,
			DbColumn whereLoanLeft, DbColumn whereLoanRight,
			DbColumn whereExclude, DbColumn whereUserComparison) {
		
		StringBuilder builder = new StringBuilder();
		
		builder.append("SELECT ");
		builder.append(type.get());
		builder.append(" AS " + ALIAS_COLUMN_TYPE);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().username.getName());
		builder.append(" AS " + ALIAS_COLUMN_USERNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().first_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_FIRSTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().last_name.getName());
		builder.append(" AS " + ALIAS_COLUMN_LASTNAME);
		builder.append(", ");
		builder.append(QueriesEnvironment.getBookTable().id.getName());
		builder.append(" AS " + ALIAS_COLUMN_BOOKID);
		builder.append(", ");
		builder.append(QueriesEnvironment.getBookTable().name.getName());
		builder.append(" AS " + ALIAS_COLUMN_BOOKNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_USERNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_FIRSTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_LASTNAME);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT_RATING);
		builder.append(", ");
		builder.append(" null ");
		builder.append(" AS " + ALIAS_COLUMN_COMMENT);
		builder.append(", ");
		builder.append(QueriesEnvironment.getLoanTable().startDate.getName());
		builder.append(" AS " + ALIAS_COLUMN_START_DATE);

		builder.append(" FROM ");
		builder.append(QueriesEnvironment.getLoanTable().getName());
		builder.append(", ");
		builder.append("(SELECT ");
		builder.append(QueriesEnvironment.getLoanTable().loanerId.getName());
		builder.append(", ");
		builder.append(QueriesEnvironment.getLoanTable().loanedId.getName());
		builder.append(", COUNT(*) AS 'num', MAX(");
		builder.append(QueriesEnvironment.getLoanTable().startDate.getName());
		builder.append(") AS 'start_date'");
		builder.append(" FROM ");
		builder.append(QueriesEnvironment.getLoanTable().getName());
		builder.append(" WHERE ");
		builder.append(innerQueryFilter.getName());
		builder.append(" = ? GROUP BY ");
		builder.append(QueriesEnvironment.getLoanTable().loanerId.getName());
		builder.append(", ");
		builder.append(QueriesEnvironment.getLoanTable().loanedId.getName());
		builder.append(" ORDER BY num DESC, start_date DESC) AS loan_count, ");
		
		builder.append(QueriesEnvironment.getBookTable().getName());
		builder.append(", ");
		builder.append(QueriesEnvironment.getUserTable().getName());
		
		builder.append(" WHERE ");
		builder.append(whereLoanLeft.getName());
		builder.append(" = ");
		builder.append("loan_count.");
		builder.append(whereLoanRight.getName().substring(
				QueriesEnvironment.getLoanTable().getName().length() + 1));
		builder.append(" AND ");
		builder.append(whereExclude.getName());
		builder.append(" != ? AND ");
		builder.append(QueriesEnvironment.getLoanTable().bookId.getName());
		builder.append(" = ");
		builder.append(QueriesEnvironment.getBookTable().id.getName());
		builder.append(" AND ");
		builder.append(whereUserComparison.getName());
		builder.append(" = ");
		builder.append(QueriesEnvironment.getUserTable().id.getName());		
		
		return builder.toString();
		
	}
	
}
